"""
Case Type   : 事务-ustore
Case Name   : 索引->事务->子事务->Update数据,长度不变->子回滚->kill数据库
Create At   : 2022/1/14
Owner       : @kyleze
Description :
    1.创建表
    2.在事务中，Update数据,长度不变
    3.kill数据库
    4.查询数据
    5.环境清理
Expect      :
    1.成功
    2.成功
    3.成功
    4.查询有数据
    5.成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class USTORE(unittest.TestCase):
    def setUp(self):
        self.pri_user = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.schema = 'schema_ustore_trans'
        self.table = 't_ustore_trans_0078'
        text = f'---{os.path.basename(__file__)} start---'
        self.log.info(text)

    def testunit_1(self):
        text = '----step1:创建表;expect:成功----'
        self.log.info(text)
        q_str = f'''drop table if exists {self.schema}.{self.table} cascade;
            create table {self.schema}.{self.table}(c_id int,c_d_id int not null,
            c_w_id int not null,c_first varchar(16) not null,c_middle char(22),
            c_last varchar(16) not null,c_street_1 varchar(20) not null,c_street_2 varchar(20),c_city varchar(20)
            not null,
            c_state char(22) not null,c_zip char(9) not null,c_phone char(16) not null,
            c_since timestamp,c_credit char(22) not null,c_credit_lim numeric(22,12),c_discount
            numeric(4,4),c_balance numeric(22,12),c_ytd_payment real not null,c_payment_cnt
            number not null,c_delivery_cnt bool not null,c_end date not null,c_data clob,c_clob clob)
            with (storage_type=ustore);
            create unique index {self.schema}.index_ustore_trans_0078_1 on
            {self.schema}.{self.table}(c_id,c_d_id);
            create index {self.schema}.index_ustore_trans_0078_02 on 
            {self.schema}.{self.table}(c_id);
            create index {self.schema}.index_ustore_trans_0078_03 on 
            {self.schema}.{self.table}(c_city);
            create index {self.schema}.index_ustore_trans_0078_04 on 
            {self.schema}.{self.table}(c_first,c_state);
            create index {self.schema}.index_ustore_trans_0078_05 on 
            {self.schema}.{self.table}(c_id,c_d_id,c_middle);
            create index {self.schema}.index_ustore_trans_0078_06 on 
            {self.schema}.{self.table}(c_id,c_d_id,c_middle,c_street_1);
            insert into {self.schema}.{self.table} 
            select * from {self.schema}.t_ustore_trans_0001;
            '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, q_res,
                      '执行失败: ' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, q_res,
                      '执行失败: ' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, q_res,
                      '执行失败: ' + text)

        text = '----step2:在事务中，Update数据,长度不变;expect:成功----'
        self.log.info(text)
        q_str = f'''start transaction;
            update {self.schema}.{self.table} set c_id=c_id+1000000,
            c_data='QVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQAFHATDOKMJOGFGSLUC
            UNVWBTBFSQZJECLBACPJQDHJCHVGBNRKJRGJRYCSGPPSOCNEVAUTZFEOSVIAXBVOBFFNJUQHLVNWUQHTGJQSBFACW
            JPBVPGTHPYXCPMNUTCJXRBXXBMRMWWXCEPWIIXVVLEYAJAUTCESLJHRSFSMSNMZJCXVCUXDWMYIJBWYWIIRSGOCWK
            TEDBBOKHYNZNCEAESUIFKGOAAFAGUGETFHBCYLKSRJUKVBUFHYBPGEKJKFEZTHNJBHFQIWBUTBXTKJKNDYYHJCHVG
            BNRKJRGJRYCSGPPSOCNEVAUTZFEOSVIAXBVOBF',
            c_clob='QVLDETANRBRBURBMZQUJSHOQNGGSMNTECCIPRIIR
            DHIRWIYNPFZCSYKXXYSCDSFQAFHATDOKMJOGFGSLUCUNVWBTBFSQZJECLBACPJQDHJCHVGBNRKJRGJRYCSGPPSOC
            NEVAUTZFEOSVIAXBVOBFFNJUQHLVNWUQHTGJQSBFACWJPBVPGTHDAFFDAFDAFDAFDASFDBMRMWWXCEPWIIXVVLEYA
            JAUTCESLJHRSFSMSNMZJCXVCUXDWMYIJBWYWIIRSGOCWKTEDBBOKHYNZNCEAESUIFKGOAAFAGUGETFHBCYLKSRJUK
            VBUFQCVBFFAXNZSSYQUIDVWEFKTKNRCHYXFPHUNQKTWNIPNSRVQSWSYMOCNOEXBABWNPMNXSVSHDSJHAZCAUVQJGV
            QJFKJJGQRCEYJMBUMKAPMCBXEASHYBPGEKJKFEZTHNJBHFQIWBUTBXTKJKNDYYHJCHVGBNRKJRGJRYCSGPPSOCNEV
            AUTZFSDFEOSVIAX' where mod(c_id,22)=0;
            savepoint point0078;
            update {self.schema}.{self.table} set c_first='barbarbar',
            c_data='QVLDETANRBRBURBMZQUjshoqnggsmnteccipriirdhirunvwbtbfsqzjeclbacpjqdhjchvgbnrkjrgj
            rycsgppsocnevautzfeosviaxbvobffnjuqhlvnwuqhtgjqsbfacwjpbvpgthdaffdafdafdafdasfdbmrmwwx
            cepwiixvvleyajautcesljhrsfsmsnmzjcxvcuxdwmyijbwywiirsgocwktedbbokhynznceaesuifkgoaafagu
            getfhbcylksrjukvbufqcvbffaxnzssyquidvwefktknrchydfsafdasfddasfdsafdsafdsafdasNOEXBABW
            NPMNXSVSHDSJHAZCAUVQJGVQJFKJJGQwiynpfzcsykxxyscdsfqafhatdokmjogfgslucunvwbtbfsqzjeclba
            cpjqdhjchvgbnrkjrgjrycsgppsocnevautzfeosviaxbvobffnjuqhlvnwuqhtgjqsbfacwjpbvpgthpyxcpmn
            utcjxrbxxbmrmwwxcepwiixvvleyajautcesljhrsfsmsnmzjcxvcuxdwmyijbwywiirsgocwktedbbokhynznc
            eaesuifkgoaaFAGUGETFHBCYLKSRJUKVBUFQCVBFFAXdasfdasfdasfdasfdsafdsaPHUNQKTWNIPNSRVQSWSYM
            OCNOEXBABWNPMNXSVSHDSJHAZCAUVQJGVQJFKJJGQRCEYJMBUMKAPMCBXEASHYBPGEKJKFEZTHNJBHFQIWBUTBX
            TKJKNDYYHJCHVGBNRKJRGJRYCSGPPSOCNEVAUTZFEOSVIAXBVOBF',
            c_clob='QVLDETANRBRBURBMZQUJSHOQN
            GGSMNTECCIPRIIRDHIRWIYNPFZCSYKXXYSCDSFQAFHATDOKMJOGFGSlucRCEYJMBUMKAPMCBXEASHYBPGEKJKda
            fdasfdsafdsafdsafdsaNDYYHJCHVGBNRKJRGJRYCSGPPSOCNEVAUTZFSDFEOSVIAX' where mod(c_id,22)=1;
            select distinct c_first,count(*) from {self.schema}.{self.table} 
            group by c_first order by c_first;
            rollback to savepoint point0078;
            select distinct c_first,count(*) from {self.schema}.{self.table} 
            group by c_first order by c_first;
        '''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('START TRANSACTION', q_res, '执行失败: ' + text)
        self.assertIn('SAVEPOINT', q_res, '执行失败: ' + text)
        self.assertIn('ROLLBACK', q_res, '执行失败: ' + text)
        self.assertIn('956 rows', q_res, '执行失败: ' + text)
        self.assertIn('1001 rows', q_res, '执行失败: ' + text)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, q_res,
                      '执行失败: ' + text)

        text = '----step3:kill-重启数据库;expect:成功----'
        self.log.info(text)
        result = self.pri_sh.restart_db_cluster()
        self.log.info(result)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue('Degraded' in status or 'Normal' in status)

        text = '----step4:查询数据;expect:1001----'
        self.log.info(text)
        q_str = f'select distinct c_first,count(*) ' \
                f'from {self.schema}.{self.table} ' \
                f'group by c_first order by c_first;'
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertNotIn('ERROR', q_res, '执行失败: ' + text)
        self.assertIn('1001 rows', q_res, '执行失败: ' + text)

    def tearDown(self):
        text = '----step5:清理环境;expect:成功----'
        self.log.info(text)
        sql = f'drop table {self.schema}.{self.table} cascade;'
        result = self.pri_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      '执行失败: ' + text)
        text = f'---{os.path.basename(__file__)} end---'
        self.log.info(text)
